/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Room;
import Hostel.HostelBean;
import java.sql.*;
import java.util.Iterator;
import java.util.Vector;

/**
 *
 * @author mishra
 */
public class Query {
    static Connection con=null;
    public static int addRoom(Vector list) throws SQLException{
        int i[]=null;
        int k=0;
        PreparedStatement pre=null;
        try{
            con=DBConnection.Connection.getConnection();
            Iterator it=list.iterator();
            while(it.hasNext())
            {
            RoomBean rb=(RoomBean)it.next();
            pre=con.prepareStatement("insert into room values(ROOMID.nextval,?,?,?,?,sysdate,?,?)");
            pre.setInt(1, rb.getHostelId());
            pre.setString(2, rb.getRoomNo());
            pre.setString(3, rb.getBedNumber());
            pre.setInt(4, rb.getBedStatus());
            pre.setString(5, rb.getUniqueRoomKey());
            pre.setInt(6, rb.getFees());
            pre.addBatch();
            }
            i=pre.executeBatch();
            if(i.length!=0)
            {
                k=1;
            }
        }catch(Exception e){
            System.out.println(e);
        }finally{
            con.close();
        }return k;
    }
    
public static int editRoom(RoomBean rb) throws SQLException{
        int i=0,k=0;
        try{
            con=DBConnection.Connection.getConnection();
            PreparedStatement pre=con.prepareStatement("update room set HOSTEL_ID=?, ROOM_NO=?, BED_NUMBER=?, BED_STATUS=?, ENTRY_DATE=sysdate, UNIQUE_ROOM_KEY=?, ROOM_FEES=?");
            pre.setInt(1, rb.getHostelId());
            pre.setString(2, rb.getRoomNo());
            pre.setString(3, rb.getBedNumber());
            pre.setInt(4, rb.getBedStatus());
            pre.setString(5, rb.getUniqueRoomKey());
            pre.setInt(6, rb.getFees());
            pre.setInt(7, rb.getRoomId());
            i=pre.executeUpdate();
            
        }catch(Exception e){
            System.out.println(e);
        }finally{
            con.close();
        }return i;
    }  
public static Vector viewAll() throws SQLException{
    Vector<Object> list=new Vector<Object>();
    try{
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("select * from ROOM left join hostel on HOSTEL.HOSTEL_ID=ROOM.HOSTEL_ID");
        ResultSet rs=pre.executeQuery();
        while(rs.next()){
            RoomBean rb=new RoomBean();
            HostelBean hb=new HostelBean();
            rb.setRoomId(rs.getInt(1));
            rb.setHostelId(rs.getInt(2));
            rb.setRoomNo(rs.getString(3));
            rb.setBedNumber(rs.getString(4));
            rb.setBedStatus(rs.getInt(5));
            rb.setEntryDate(rs.getDate(6));
            rb.setUniqueRoomKey(rs.getString(7));
            rb.setFees(rs.getInt(8));
            hb.setHotelId(rs.getInt(9));
            hb.setHostelName(rs.getString(10));
            hb.setHostelType(rs.getString(11));
            hb.setHostelAddress(rs.getString(12));
            hb.setWardenId(rs.getInt(13));
            hb.setMobile(rs.getString(14));
            hb.setPhone(rs.getString(15));
            hb.setEntryDate(rs.getDate(16));
            rb.setHostelBean(hb);
            list.add(rb);
        }
    }catch(Exception e){
        System.out.println(e);
    }
        finally{con.close();
    }return list;
}
public static RoomBean viewSpecific(String cname, int val) throws SQLException{
    RoomBean rb=new RoomBean();
    try{
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("select * from ROOM left join hostel on HOSTEL.HOSTEL_ID=ROOM.HOSTEL_ID where room."+cname+"="+val+"");
        ResultSet rs=pre.executeQuery();
        while(rs.next()){
            
            HostelBean hb=new HostelBean();
            rb.setRoomId(rs.getInt(1));
            rb.setHostelId(rs.getInt(2));
            rb.setRoomNo(rs.getString(3));
            rb.setBedNumber(rs.getString(4));
            rb.setBedStatus(rs.getInt(5));
            rb.setEntryDate(rs.getDate(6));
            rb.setUniqueRoomKey(rs.getString(7));
            rb.setFees(rs.getInt(8));
            hb.setHotelId(rs.getInt(9));
            hb.setHostelName(rs.getString(10));
            hb.setHostelType(rs.getString(11));
            hb.setHostelAddress(rs.getString(12));
            hb.setWardenId(rs.getInt(13));
            hb.setMobile(rs.getString(14));
            hb.setPhone(rs.getString(15));
            hb.setEntryDate(rs.getDate(16));
            rb.setHostelBean(hb);
            
        }
    }catch(Exception e){
        System.out.println(e);
    }
        finally{con.close();
    }return rb;
}
public static int delete(String roomNo)throws SQLException{
    int i=0;
    try{
       con=DBConnection.Connection.getConnection();
       PreparedStatement pre=con.prepareStatement("delete from room where ROOM_NO=?");
       pre.setString(1, roomNo);
       i=pre.executeUpdate();
        
    }catch(Exception e){
        System.out.println(e);
    }finally{
        con.close();
    }return i;
}
public static int maxId() throws SQLException{
    int i=0;
    try{
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("select max(ROOM_ID) from room");
        ResultSet rs=pre.executeQuery();
        if(rs.next()){
            i=rs.getInt(1);
        }
        
    }catch(Exception e){
        System.out.println(e);
    }finally{
        con.close();
    }return i;
}
public static int checkHostelId(int id) throws SQLException{
    int i=0;
    try{
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("select count(hostel_id) from room where hostel_id=?");
        pre.setInt(1, id);
        ResultSet rs=pre.executeQuery();
        if(rs.next()){
            i=rs.getInt(1);
        }
        
    }catch(Exception e){
        System.out.println(e);
    }finally{
        con.close();
    }return i;
}
public static Vector viewHostel()throws SQLException{
    Vector<Object> list=new Vector<Object>();
    try{
        
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("select room.hostel_id,count(distinct(room.room_no))Room,count(room.room_no)bed,(select hostel.hostel_name from hostel where hostel.hostel_id=room.hostel_id)hostel_name,(select hostel.hostel_type from hostel where hostel.hostel_id=room.hostel_id)hostel_type from room group by room.hostel_id");
        ResultSet rs=pre.executeQuery();
        while(rs.next())
        {
            Vector<Object> list1=new Vector<Object>();
            list1.add(rs.getInt(1));
            list1.add(rs.getString(2));
            list1.add(rs.getString(3));
            list1.add(rs.getString(4));
            list1.add(rs.getString(5));
            PreparedStatement pre1=con.prepareStatement("select count(room.bed_status)availability from room where bed_status=0 and hostel_id=?");
            pre1.setInt(1,rs.getInt(1));
            ResultSet rr=pre1.executeQuery();
            if(rr.next())
            {
            list1.add(rr.getInt(1));
            }
            else
            {
                list1.add("0");
            }
            list.add(list1);
        }
        
    }catch(Exception e){
        System.out.println(e);
    }finally{
        con.close();
    }return list;
}
public static Vector viewHostelRoom(int hid)throws SQLException{
    Vector<Object> list=new Vector<Object>();
    try{
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("select room_no,count(bed_number),(sum(room_fees)/count(bed_number))rent from ROOM where hostel_id=? group by room_no");
        pre.setInt(1, hid);
        ResultSet rs=pre.executeQuery();
        while(rs.next())
        {
            Vector<Object> list1=new Vector<Object>();
            list1.add(rs.getString(1));
            list1.add(rs.getString(2));
            list1.add(rs.getInt(3));
            PreparedStatement pre1=con.prepareStatement("select count(bed_status)bed from room where room_no=? and bed_status=0 group by room_no");
            pre1.setString(1,rs.getString(1));
            ResultSet rr=pre1.executeQuery();
            if(rr.next())
            {
            list1.add(rr.getInt(1));
            }
            else
            {
                list1.add("0");
            }
            list.add(list1);
        }
        
    }catch(Exception e){
        System.out.println(e);
    }finally{
        con.close();
    }return list;
}
public static Vector viewBeds(RoomBean rb)throws SQLException{

    Vector<Object> list=new Vector<Object>();
    try{
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("select BED_NUMBER, BED_STATUS from room where room_no=? order by BED_NUMBER");
        pre.setString(1, rb.getRoomNo());
        ResultSet rs=pre.executeQuery();
        while(rs.next()){
            RoomBean rb1=new RoomBean();
            rb1.setBedNumber(rs.getString(1));
            rb1.setBedStatus(rs.getInt(2));
            list.add(rb1);
        }
    }catch(Exception e){
        System.out.println(e);
    }finally{
        con.close();
    }return list;
}

public static int addBed(RoomBean rb,int bedno) throws SQLException
{
    int i[]=null;
    int k=0;
    try
    {
       con=DBConnection.Connection.getConnection();
       PreparedStatement pre=con.prepareStatement("insert into room values(ROOMID.nextval,?,?,?,?,sysdate,?,?)");
       for(int j=1;j<=bedno;j++)
       {
       if(!bedNoExist(rb.getRoomNo(),"B"+j))
       {
       pre.setInt(1,rb.getHostelId());
       pre.setString(2,rb.getRoomNo());
       pre.setString(3,"B"+j);
       pre.setInt(4,0);
       pre.setString(5,rb.getHostelId()+"-"+rb.getRoomNo()+"-B"+j);
       pre.setInt(6,rb.getFees());
       pre.addBatch();
       }
       else
       {
           PreparedStatement pre1=con.prepareStatement("update ROOM set ROOM_FEES=? where ROOM_NO=?");
           pre1.setInt(1, rb.getFees());
           pre1.setString(2, rb.getRoomNo());
           k=pre1.executeUpdate();
       }
       }
       i=pre.executeBatch();
       if(i.length>0)
       {
           k=1;
       }
    }
    catch(Exception e)
    {
        return k;
    }
    finally
    {
        con.close();
    }
    return k;
}

public static boolean bedNoExist(String roomno,String bedno)
{
    boolean val=false;
    try
    {
       PreparedStatement pre=con.prepareStatement("select count(*) from room where room_no=? and bed_number=?");
       pre.setString(1,roomno);
       pre.setString(2,bedno);
       ResultSet rs=pre.executeQuery();
       while(rs.next())
       {
           if(rs.getInt(1)>0)
           {
               val=true;
           }
           else
           {
               val=false;
           }
       }
    }
    catch(Exception e)
    {
        return val;
    }
    return val;
}
public static int deleteBed(RoomBean rb) throws SQLException{
    int i=0;
    try{
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("delete from room where ROOM_NO=? and BED_NUMBER=?");
        pre.setString(1, rb.getRoomNo());
        pre.setString(2, rb.getBedNumber());
        i=pre.executeUpdate();
    }catch(Exception e){
        System.out.println(e);
    }finally{
        con.close();
    }return i;
}
public static Vector getRoomNo(int hostelId)throws SQLException{
    Vector<Object> list= new Vector<Object>();
    try{
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("select distinct(ROOM_NO) from ROOM where HOSTEL_ID=?");
        pre.setInt(1, hostelId);
        ResultSet rs=pre.executeQuery();
        while(rs.next()){
            RoomBean rb=new RoomBean();
            rb.setRoomNo(rs.getString(1));
            list.add(rb);
        }
    }catch(Exception e){
        System.out.println(e);
    }finally{
        con.close();
    }return list;
}
public static Vector getBedNumber(String roomno)throws SQLException{
    Vector<Object> list= new Vector<Object>();
    try{
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("select BED_NUMBER, ROOM_ID from ROOM where ROOM_NO=? and BED_STATUS=0");
        pre.setString(1, roomno);
        ResultSet rs=pre.executeQuery();
        while(rs.next()){
            RoomBean rb=new RoomBean();
            rb.setBedNumber(rs.getString(1));
            rb.setRoomId(rs.getInt(2));
            list.add(rb);
        }
    }catch(Exception e){
        System.out.println(e);
    }finally{
        con.close();
    }return list;
}
public static float getExtraAmount(int roomid)throws SQLException{
    float i=0;
    try{
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("select ROOM_FEES from room where room_id=?");
        pre.setInt(1, roomid);
        ResultSet rs=pre.executeQuery();
        if(rs.next()){
            i=rs.getFloat(1);
        }
    }catch(Exception e){
        System.out.println(e);
    }finally{
        con.close();
    }return i;
}
//--------------------------For Withrow------------------------------
public static int updateStatus(int rid)throws SQLException{
    int i=0;
    try{
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("update room set BED_STATUS=0 where ROOM_ID=?");
        pre.setInt(1, rid);
        i=pre.executeUpdate();
    }catch(Exception e){
        return i;
    }finally{
        con.close();
    }return i;
}
//-----------------------For Allot Room ------------------------
public static int updateStatusforWithdraw(int rid)throws SQLException{
    int i=0;
    try{
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("update room set BED_STATUS=1 where ROOM_ID=?");
        pre.setInt(1, rid);
        i=pre.executeUpdate();
    }catch(Exception e){
        return i;
    }finally{
        con.close();
    }return i;
}
public static Vector ViewRoomAccHostel(int hostelid) throws SQLException{
    Vector<Object> list=new Vector();
    try{
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("Select distinct (room_no) from ROOM where hostel_id=? order by room_no");
        pre.setInt(1, hostelid);
        ResultSet rs=pre.executeQuery();
        while(rs.next()){
            RoomBean rb=new RoomBean();
            rb.setRoomNo(rs.getString(1));
            list.add(rb);
        }
    }catch(Exception e){
        System.out.println(e);
    }
        finally{con.close();
    }return list;
}

public static Vector ViewRoomAccRoomNo(String roomNo) throws SQLException{
    Vector<Object> list=new Vector();
    try{
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("Select * from ROOM where room_no=? order by room_no");
        pre.setString(1, roomNo);
        ResultSet rs=pre.executeQuery();
        while(rs.next()){
            RoomBean rb=new RoomBean();
            rb.setRoomId(rs.getInt(1));
            rb.setHostelId(rs.getInt(2));
            rb.setRoomNo(rs.getString(3));
            rb.setBedNumber(rs.getString(4));
            rb.setBedStatus(rs.getInt(5));
            rb.setEntryDate(rs.getDate(6));
            rb.setUniqueRoomKey(rs.getString(7));
            rb.setFees(rs.getInt(8));
            list.add(rb);
        }
    }catch(Exception e){
        System.out.println(e);
    }
        finally{con.close();
    }return list;
}
public static int getAdditionalAmt(int roomid)throws SQLException{
     int fees=0;
    try{
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("select ROOM_FEES from ROOM where ROOM_ID=?");
        pre.setInt(1, roomid);
        ResultSet rs=pre.executeQuery();
        while(rs.next()){
            fees=rs.getInt(1);
        }
    }catch(Exception e){
        System.out.println(e);
    }finally{
        con.close();
    }return fees;
}
}
